我對SQL中的with一直感到很好奇，
因為他也可以用在遞迴的處理，
不過大大這個例子應該只是利用with先串出最後一段SQL需要的表吧

http://sqlfiddle.com/#!7/9387c/1
我用Sqlite做。

建一個tmp表，新增一欄，名稱為serialNo，性質為遞增的整數。

CREATE TABLE t_tmp (serialNo integer primary key autoincrement, userid int, result text);

把資料以userid排序，塞進tmp表中。

INSERT INTO t_tmp(userid, result) select userid, result from testtable order by userid;

從tmp表中抽出[result] = 'win'的紀錄，並以下一筆紀錄做self join。若連贏者，serialNo將會相差1。把serialNo的差異加總再加1即為連贏的次數。

SELECT  A.userid, sum(B.serialNo - A.serialNo) + 1 AS win, 'WIN'
FROM
(select * from t_tmp where result = 'win') A
LEFT JOIN
(select * from t_tmp where result = 'win') B
ON A.userid = B.userid AND A.serialNo = B.serialNo - 1 
WHERE NOT B.id IS NULL
GROUP BY A.userid;

如法炮製連輸的次數，最後用UNION把連贏與連輸的資料串接，並剔除重複的資料。

SELECT  A.userid, 'WIN' as Result, sum(B.serialNo - A.serialNo) + 1 AS maxNo
FROM
(select * from t_tmp where result = 'win') A
LEFT JOIN
(select * from t_tmp where result = 'win') B
ON A.userid = B.userid AND A.serialNo = B.serialNo - 1 
WHERE NOT B.serialNo IS NULL
GROUP BY A.userid
UNION
SELECT  A.userid, 'LOSS', sum(B.serialNo - A.serialNo) + 1 AS loss
FROM
(select * from t_tmp where result = 'loss') A
LEFT JOIN
(select * from t_tmp where result = 'loss') B
ON A.userid = B.userid AND A.serialNo = B.serialNo - 1 
WHERE NOT B.serialNo IS NULL
GROUP BY A.userid;

MS SQL，我想了好久，不知道正不正確，分享一下。

先幫資料編 id，模擬時間欄位，後續排序用。

DECLARE @Temp TABLE
(
	id int,
	userid int, 
	result varchar(4)
)

INSERT INTO @Temp
	(id, userid, result)
VALUES
	(1, 1, 'win'),
	(2, 1, 'loss'),
	(3, 1, 'win'),
	(4, 1, 'win'),
	(5, 2, 'win'),
	(6, 1, 'loss'),
	(7, 2, 'win'),
	(8, 1, 'loss'),
	(9, 2, 'win'),
	(10, 3, 'win'),
	(11, 2, 'loss'),
	(12, 2, 'loss'),
	(13, 2, 'loss'),
	(14, 3, 'win'),
	(15, 3, 'win')

接下來利用子查詢，將連續的資料標記 flag，
然後用 userid, result, flag 這三個欄位分組，算出連勝或連敗的次數，
最後只取連勝或連敗次數大於一的資料，就可以得到期望的結果。

SELECT A.userid, A.result, A.recode
FROM
(
	SELECT A.userid, 
           A.result, 
           A.flag,
		   COUNT(A.userid) AS recode
	FROM 
	(
		SELECT A.*,
			(
				SELECT COUNT(B.id)
				FROM @Temp AS B
				WHERE B.userid=A.userid AND 
                      B.id < A.id AND 
                      B.result<>A.result
			) AS flag
		FROM @Temp AS A
	) AS A
	GROUP BY A.userid, A.result, A.flag
) AS A
WHERE A.recode > 1
ORDER BY A.userid, A.flag

結果

包了四層的 SELECT。

發現好像誤會題意了，寫成統計每位玩家的連勝或連敗紀錄歷程，題目是要統計每位玩家的最大連勝或最大連敗次數，所以再多一層排序取最大值。

排序的部分因為經過 神Q超人 前幾周一連串的排序考試，所以很快就寫出來了。

SELECT A.userid, A.result, A.recode
FROM
(
	SELECT A.userid,
		   A.result,
		   A.recode,
		   ROW_NUMBER() OVER (PARTITION BY userid, result 
               ORDER BY recode DESC) AS rerank
	FROM
	(
		SELECT A.userid,
		       A.result,
			   A.flag,
			   COUNT(A.userid) AS recode
		FROM 
		(
			SELECT A.*,
				(
					SELECT COUNT(B.id)
					FROM @Temp AS B
					WHERE B.userid=A.userid AND 
                          B.id < A.id AND 
                          B.result<>A.result
				) AS flag
			FROM @Temp AS A
		) AS A
		GROUP BY A.userid, A.result, A.flag
	) AS A
	WHERE A.recode > 1
) AS A 
WHERE A.rerank=1
ORDER BY A.userid, A.result DESC

最後變成了五層 SELECT。

如果資料庫支援 Windows function 可以使用下面語法

因為是連續範圍問題

1. 在 RNT CTE 中 先幫原始資料 先建立一個連續編號 ROW_NUMBER() OVER(ORDER BY (select NULL)) rn

Results:

| userid | result | rn |
|--------|--------|----|
|      1 |    win |  1 |
|      1 |   loss |  2 |
|      1 |    win |  3 |
|      1 |    win |  4 |
|      2 |    win |  5 |
|      1 |   loss |  6 |
|      2 |    win |  7 |
|      1 |   loss |  8 |
|      2 |    win |  9 |
|      3 |    win | 10 |
|      2 |   loss | 11 |
|      2 |   loss | 12 |
|      2 |   loss | 13 |
|      3 |    win | 14 |
|      3 |    win | 15 |

2. GrpT CTE 中 取得分組邊跟連續勝利編號差別 並設置為grp欄位(之後可以來group by)

Results:

| userid | result | rn | grp |
|--------|--------|----|-----|
|      1 |    win |  1 |   0 |
|      1 |    win |  3 |   1 |
|      1 |    win |  4 |   1 |
|      1 |   loss |  2 |   1 |
|      1 |   loss |  6 |   3 |
|      1 |   loss |  8 |   3 |
|      2 |    win |  5 |   0 |
|      2 |    win |  7 |   0 |
|      2 |    win |  9 |   0 |
|      2 |   loss | 11 |   3 |
|      2 |   loss | 12 |   3 |
|      2 |   loss | 13 |   3 |
|      3 |    win | 10 |   0 |
|      3 |    win | 14 |   0 |
|      3 |    win | 15 |   0 |

全部查詢語法

;WITH RNT AS(
 SELECT *,ROW_NUMBER() OVER(ORDER BY (select NULL)) rn
 FROM TestTable
),
GrpT AS(
  SELECT *,
    (ROW_NUMBER() OVER(PARTITION BY userid ORDER BY rn) - 
         ROW_NUMBER() OVER(PARTITION BY userid,result ORDER BY rn)) grp 
  FROM RNT
)

SELECT userid,result,COUNT(*) 'recode'
FROM GrpT T1
GROUP BY grp,userid,result
HAVING COUNT(*) > 1
ORDER BY userid

SQLFiddle

| userid | result | recode |
|--------|--------|--------|
|      1 |    win |      2 |
|      1 |   loss |      2 |
|      2 |    win |      3 |
|      2 |   loss |      3 |
|      3 |    win |      3 |